-- exec sp_search_po_for_receipts '%', -1, -1, '-1'
ALTER   PROC sp_search_po_for_receipts @poNumber VARCHAR(50), @vendorId INT, @processStepId INT, @year VARCHAR(10)
AS
BEGIN
	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)
	DECLARE @PurchaseOrder_ID INT
	DECLARE @PurchaseOrder_Number VARCHAR(50)
	DECLARE @PurchaseOrder_Date VARCHAR(50)
	DECLARE @Vendor_ID INT
	DECLARE @Vendor_Name VARCHAR(50)
	DECLARE @PoType INT
	DECLARE @PO_Status VARCHAR(50)
	DECLARE @Valid_From VARCHAR(50)
	DECLARE @Valid_To VARCHAR(50)
	DECLARE @Max_Value NUMERIC
	DECLARE @Count INT
	DECLARE @PurchaseOrder_Type INT

	DECLARE @results TABLE ( PurchaseOrder_Id INT, PONumber VARCHAR(50), Vendor_Name VARCHAR(50), PODate VARCHAR(50), PURCHASEORDER_TYPE INT )
	SET @Count = 0	

	SET @SQLString = N'DECLARE PurchaseOrderCursor CURSOR FOR SELECT PurchaseOrder_ID, PurchaseOrder_No, V_Name, CONVERT( VARCHAR, PO_Date, 101 ) AS PO_Date, PurchaseOrder_Type FROM PurchaseOrder_Table, Vendor WHERE PurchaseOrder_Table.Vendor_Id = Vendor.Vendor_Id /*AND PurchaseOrder_Type <> 1*/ AND PO_Status <> ''New'' AND PO_Status <> ''Cancelled'' And PO_Status <> ''Closed'' '
	SET @ParamDefinition = N'@Prm_PONumber VARCHAR(50), @Prm_Vendor_ID INT, @Prm_ProcessStep_ID INT, @Prm_Year VARCHAR(10)'

	IF ( @poNumber IS NOT NULL AND LTRIM(RTRIM( @poNumber ) ) <> '' )
	BEGIN
		SET @SQLString = @SQLString + ' AND PurchaseOrder_No like @Prm_PONumber '
	END

	IF ( @vendorId IS NOT NULL AND @vendorId <> -1 )
	BEGIN
		SET @SQLString = @SQLString + ' AND PurchaseOrder_Table.Vendor_Id = @Prm_Vendor_ID'
	END

	IF ( @year IS NOT NULL AND LTRIM(RTRIM( @year ) ) <> '-1' )
	BEGIN
		SET @SQLString = @SQLString + ' AND YEAR( PO_Date ) = @Prm_Year'
	END

	SET @SQLString = @SQLString + ' ORDER BY PO_Date, PurchaseOrder_No'

	EXECUTE sp_executesql @SQLString, @ParamDefinition, @Prm_PONumber = @poNumber , @Prm_Vendor_ID = @vendorId, @Prm_ProcessStep_Id = @processStepId, @Prm_Year = @year
	
	OPEN PurchaseOrderCursor
	FETCH NEXT FROM PurchaseOrderCursor INTO @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PurchaseOrder_Type
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @processStepId IS NOT NULL AND @processStepId <> -1 )
		BEGIN
			SET @Count = 0
			SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND ProcessStep_Id = @processStepId AND Status <> 'Closed' AND Status <> 'Cancelled' AND Status <> 'New'
			IF @Count <> 0
			BEGIN
				INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, PURCHASEORDER_TYPE )
				VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PurchaseOrder_Type )
			END
		END
 		ELSE
		BEGIN
			
			SET @Count = 0
			IF ( @PurchaseOrder_Type = 0 ) --STANDARD PO
			 BEGIN
				SELECT @Count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE PurchaseOrder_Id = @PurchaseOrder_ID AND Status <> 'Closed' AND Status <> 'Cancelled' AND Status <> 'New'
				IF @Count <> 0
				BEGIN
					INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, PURCHASEORDER_TYPE )
					VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PurchaseOrder_Type )
				END
			 END
			ELSE --OPEN PO
			 BEGIN
				INSERT INTO @results ( PurchaseOrder_Id, PONumber, Vendor_Name, PODate, PURCHASEORDER_TYPE )
				VALUES ( @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PurchaseOrder_Type )
			 END
		END

		FETCH NEXT FROM PurchaseOrderCursor INTO @PurchaseOrder_ID, @PurchaseOrder_Number, @Vendor_Name, @PurchaseOrder_Date, @PurchaseOrder_Type
	END
	CLOSE PurchaseOrderCursor
	DEALLOCATE PurchaseOrderCursor

	SELECT * FROM @results
END